﻿using Commission.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Commission.DAL
{
    public class TransactionRepository : GenericRepository<TransactionHistory>
    {
        public TransactionRepository(SchoolContext context)
            : base(context)
        {
        }

        public int UpdateCourseCredits(int multiplier)
        {
            return context.Database.ExecuteSqlCommand("UPDATE Course SET Credits = Credits * {0}", multiplier);
        }

        public List<TransactionHistory> GetTransactions(int customerId)
        {
            return context.TransactionHistories
                .Include("SponsoredCustomer")
                .Where(s => 
                    s.CustomerID == customerId 
                    && s.AccountType != AccountType.MinusPoint
                    //&& s.TransactionStatus != TransactionStatus.Cancel
                    && s.DeletedAt == null
                    )
                .ToList<TransactionHistory>();
        }

        public TransactionHistory GetTransactionById(int transactionId)
        {
            return context.TransactionHistories
                .Where(s => s.ID == transactionId)
                .FirstOrDefault<TransactionHistory>();
        }

        public decimal GetAmount(int customerId)
        {
            var query = "SELECT Sum(Amount) AS Amount"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " And " + GetAllCondition()
                + " GROUP BY CustomerID";
            SqlParameter param1 = new SqlParameter("CustomerID", customerId);

            decimal amount = context.Database.SqlQuery<decimal>(query, param1).FirstOrDefault();

            return amount;
        }

        public List<object> GetTransactions()
        {
            var query = "SELECT *"
                + " FROM TransactionHistory"
                + " WHERE " + GetCondition();

            var list = context.Database.SqlQuery<object>(query).ToList();

            return list;
        }

        private string GetCondition()
        {
            return 
                  " AccountType != " + (int)AccountType.MinusPoint
                + " And DeletedAt Is Null ";
        }

        private string GetAllCondition()
        {
            return
                  " AccountType != " + (int)AccountType.MinusPoint
                + " And TransactionStatus != " + (int)TransactionStatus.Cancel
                + " And DeletedAt Is Null ";
        }
    }
}